begin tran
use pubs
insert authors
(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
values
('000-00-002','***No Current Author***',
'','None','None','None','XX','99999',0) 
insert titles
(title_id,title,type,pub_id,price,advance,royalty,ytd_sales,notes,pubdate)
values
('ZZ9999','***No Current Title***',
'None','9999',0.00,0,0,0,'None','1900.01.01')
insert titleauthor values ('341-22-1782','ZZ9999',0,0)
insert titleauthor values ('527-72-3246','zz9999',0,0)
insert titleauthor values ('724-08-9931','ZZ9999',0,0)
insert titleauthor values ('893-72-1158','ZZ9999',0,0)

insert titleauthor values('000-00-0002','MC3026',0,0)

select
'Author'=rtrim(au_lname)+','+au_fname,
'Title'=title
from
authors as a 
inner join titleauthor as ta on a.au_id=ta.au_id
inner join titles as t on t.title_id=ta.title_id
where
a.state<>'CA'
order by 1

Author                                                        Title                                                                            
------------------------------------------------------------- -------------------------------------------------------------------------------- 
Blotchet-Halls,Reginald                                       Fifty Years in Buckingham Palace Kitchens
DeFrance,Michel                                               The Gourmet Microwave
del Castillo,Innes                                            Silicon Valley Gastronomic Treats
Greene,Morningstar                                            ***No Current Title***
Panteley,Sylvia                                               Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Ringer,Albert                                                 Is Anger the Enemy?
Ringer,Albert                                                 Life Without Fear
Ringer,Anne                                                   The Gourmet Microwave
Ringer,Anne                                                   Is Anger the Enemy?
Smith,Meander                                                 ***No Current Title***

(10 row(s) affected)